Data Engineering Path · Data Modelling
LINKEDIN CASE STUDY
Step 2: Entity Identification — LinkedIn
Detailed Entity Analysis
🔹 USERS — Professional profiles
Purpose: Store professional profile data — the core identity on LinkedIn
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| user_id | INT (PK) | Unique identifier |
| VARCHAR(100), UNIQUE | Login email address | |
| password_hash | VARCHAR(255) | Encrypted password |
| first_name | VARCHAR(50) | First name |
| last_name | VARCHAR(50) | Last name |
| vanity_url | VARCHAR(100), UNIQUE | Custom profile URL slug (e.g. john-doe) |
| headline | VARCHAR(220) | Professional headline (e.g. "SDE-3 at Google") |
| summary | TEXT | About/summary section (max 2600 chars) |
| profile_photo_url | VARCHAR(500) | Profile picture URL |
| background_photo_url | VARCHAR(500) | Banner/cover image URL |
| location | VARCHAR(100) | Current city/region |
| country | VARCHAR(50) | Country |
| industry | VARCHAR(100) | Professional industry |
| current_company | VARCHAR(200) | Current employer (denormalized for fast display) |
| connections_count | INT | Number of connections (denormalized) |
| followers_count | INT | Number of followers (denormalized) |
| is_open_to_work | BOOLEAN | Job seeking flag |
| is_hiring | BOOLEAN | Actively hiring flag |
| profile_visibility | ENUM | public, connections_only, private |
| created_at | TIMESTAMP | Account creation time |
| last_active_at | TIMESTAMP | Last login/activity timestamp |
| is_verified | BOOLEAN | Identity verification badge |
| is_premium | BOOLEAN | Premium subscriber flag |
Business Rules:
- Email and vanity_url must be globally unique
- Headline max 220 characters
- Summary max 2,600 characters
connections_countdenormalized for O(1) display on profile
🔹 WORK_EXPERIENCE — Employment history
Purpose: Employment history — multiple positions per user, multiple users per company
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| experience_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Profile owner |
| company_id | INT (FK) | Linked company (if exists in companies table) |
| company_name | VARCHAR(200) | Company name (text fallback if no company page) |
| job_title | VARCHAR(200) | Position title |
| employment_type | ENUM | full_time, part_time, contract, internship, freelance, self_employed |
| location | VARCHAR(100) | Work location |
| start_date | DATE | Position start date |
| end_date | DATE | Position end date (NULL = current) |
| is_current | BOOLEAN | Whether this is the active position |
| description | TEXT | Role description and achievements |
| skills_used | TEXT | Comma-separated skills (denormalized for display) |
Business Rules:
- A user can have multiple positions at the same company (promotions)
- Overlapping positions at different companies are allowed (consulting/advisory)
is_current = TRUEpositions drive the profile headline and "X employees at Company" countcompany_idis nullable — not all employers have a LinkedIn Company Page
🔹 EDUCATION — Academic history
Purpose: Academic history
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| education_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Profile owner |
| institution_name | VARCHAR(200) | School/university name |
| degree | VARCHAR(100) | Degree type (B.Tech, MBA, PhD, etc.) |
| field_of_study | VARCHAR(100) | Major/concentration |
| grade | VARCHAR(20) | GPA or grade (optional) |
| activities | TEXT | Extracurricular activities and societies |
| description | TEXT | Additional details |
| start_year | INT | Enrollment year |
| end_year | INT | Graduation year (NULL = ongoing) |
Business Rules:
- Multiple education entries per user
- Used for "Alumni" search filters and "People You May Know" recommendations
🔹 COMPANIES — Company/organization profiles
Purpose: Company/organization profiles (Company Pages)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| company_id | INT (PK) | Unique identifier |
| name | VARCHAR(200) | Company name |
| vanity_url | VARCHAR(100), UNIQUE | Custom company URL slug |
| logo_url | VARCHAR(500) | Company logo |
| cover_photo_url | VARCHAR(500) | Company banner image |
| industry | VARCHAR(100) | Primary industry classification |
| company_size | ENUM | 1-10, 11-50, 51-200, 201-500, 501-1000, 1001-5000, 5001-10000, 10001+ |
| company_type | ENUM | public, private, nonprofit, government, educational, self_employed |
| headquarters | VARCHAR(100) | HQ city/country |
| founded_year | INT | Year established |
| website | VARCHAR(200) | Company website URL |
| description | TEXT | About the company |
| specialties | TEXT | Areas of expertise (comma-separated) |
| followers_count | INT | Number of followers (denormalized) |
| employee_count_on_linkedin | INT | Employees with is_current position at this company (denormalized) |
| created_at | TIMESTAMP | Page creation time |
Business Rules:
employee_count_on_linkedinis a denormalized aggregate ofwork_experience.is_current = TRUE- Company pages can have multiple admins with different roles
- Followers see company posts in their feed
🔹 COMPANY_ADMINS — Company page administrators
Purpose: Users who administer a Company Page
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| admin_id | INT (PK) | Unique identifier |
| company_id | INT (FK) | Company page |
| user_id | INT (FK) | Admin user |
| role | ENUM | super_admin, content_admin, analyst, recruiter |
| assigned_at | TIMESTAMP | When admin access was granted |
| assigned_by | INT (FK) | Who granted access |
Business Rules:
- Super Admin: full control (manage admins, edit page, post, analytics)
- Content Admin: post and comment as company
- Analyst: view analytics only
- Recruiter: manage job postings
🔹 COMPANY_FOLLOWERS — Users following companies
Purpose: Unidirectional follow relationship between users and companies
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| follow_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Follower |
| company_id | INT (FK) | Company being followed |
| followed_at | TIMESTAMP | When the follow occurred |
Business Rules:
- Unique constraint on
(user_id, company_id) - Followers see company posts in their news feed
- Auto-follow can be triggered when adding a work experience at a company
🔹 CONNECTIONS — Bidirectional professional connections
Purpose: Bidirectional professional connections (mutual, like Facebook friends)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| connection_id | INT (PK) | Unique identifier |
| requester_id | INT (FK) | User who sent the connection request |
| receiver_id | INT (FK) | User who received the request |
| status | ENUM | pending, accepted, rejected, withdrawn |
| note | VARCHAR(300) | Personal note attached to the request |
| requested_at | TIMESTAMP | Request timestamp |
| responded_at | TIMESTAMP | Accept/reject timestamp |
Business Rules:
- Bidirectional: accepted connection means mutual visibility
- Max 30,000 connections per user
- Personal note max 300 characters
- Withdrawn requests can be re-sent after 3 weeks
- Unique constraint on
(requester_id, receiver_id)to prevent duplicates
🔹 FOLLOWS — Unidirectional follow relationships
Purpose: Unidirectional follow (see someone's public posts without connecting)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| follow_id | INT (PK) | Unique identifier |
| follower_id | INT (FK) | User who is following |
| followed_id | INT (FK) | User being followed |
| followed_at | TIMESTAMP | When the follow occurred |
Business Rules:
- Asymmetric: A can follow B without B following A
- Connecting with someone auto-creates a mutual follow
- Followers see the followed user's public posts
- Unique constraint on
(follower_id, followed_id)
🔹 SKILLS — Global skills taxonomy
Purpose: Global standardized skills taxonomy (40,000+ skills)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| skill_id | INT (PK) | Unique identifier |
| name | VARCHAR(100), UNIQUE | Skill name (e.g. "Python", "Machine Learning") |
| category | VARCHAR(100) | Skill category (e.g. "Programming Languages", "Data Science") |
Business Rules:
- Curated master list — users cannot create arbitrary skills
- Used for job posting requirements, profile search filters, and endorsements
- Skills have categories for faceted search
🔹 USER_SKILLS — Skills on a user's profile
Purpose: Skills listed on a user's profile (M:N junction between users and skills)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| user_skill_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Profile owner |
| skill_id | INT (FK) | Skill reference |
| display_order | INT | Position on the profile (user-customizable) |
| endorsement_count | INT | Number of endorsements (denormalized) |
| is_assessment_passed | BOOLEAN | Whether LinkedIn Skill Assessment badge earned |
| added_at | TIMESTAMP | When the skill was added |
Business Rules:
- Max ~50 skills per profile
- Unique constraint on
(user_id, skill_id) endorsement_countdenormalized from theendorsementstable for O(1) display
🔹 ENDORSEMENTS — Skill endorsement from connections
Purpose: One-click skill endorsements from connections
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| endorsement_id | INT (PK) | Unique identifier |
| user_skill_id | INT (FK) | The specific skill on the specific user's profile |
| endorsed_by | INT (FK) | User who endorsed the skill |
| endorsed_at | TIMESTAMP | When the endorsement was given |
Business Rules:
- One endorsement per endorser per user-skill (cannot endorse the same skill twice)
- Unique constraint on
(user_skill_id, endorsed_by) - Only connections can endorse each other
- Endorsement count is denormalized to
user_skills.endorsement_count
🔹 RECOMMENDATIONS — Written professional testimonials
Purpose: Written professional testimonials between connections
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| recommendation_id | INT (PK) | Unique identifier |
| author_id | INT (FK) | User who wrote the recommendation |
| recipient_id | INT (FK) | User receiving the recommendation |
| relationship | ENUM | managed_directly, reported_to, worked_together, was_client, was_mentor, was_student |
| position_at_time | VARCHAR(200) | Recipient's role when they worked together |
| company_at_time | VARCHAR(200) | Company where they worked together |
| content | TEXT | The recommendation text |
| status | ENUM | pending, accepted, hidden |
| created_at | TIMESTAMP | When the recommendation was written |
Business Rules:
- Recommendations must be accepted by the recipient to be visible on their profile
- Recipients can hide accepted recommendations without deleting them
- Relationship context provides credibility signal
🔹 CERTIFICATIONS — Professional certifications and licenses
Purpose: Professional certifications and licenses
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| certification_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Profile owner |
| name | VARCHAR(200) | Certification name (e.g. "AWS Solutions Architect") |
| issuing_organization | VARCHAR(200) | Certifying body (e.g. "Amazon Web Services") |
| issue_date | DATE | When the certification was earned |
| expiration_date | DATE | When it expires (NULL = no expiration) |
| credential_id | VARCHAR(100) | Certification reference number |
| credential_url | VARCHAR(500) | Verification URL |
🔹 PROJECTS — Portfolio projects
Purpose: Portfolio projects showcased on profile
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| project_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Profile owner |
| name | VARCHAR(200) | Project name |
| description | TEXT | Project details |
| url | VARCHAR(500) | Project URL (GitHub, live demo, etc.) |
| start_date | DATE | Project start date |
| end_date | DATE | Project end date (NULL = ongoing) |
| associated_experience_id | INT (FK) | Linked work experience (optional) |
🔹 POSTS — Feed content (text, image, video, article, poll)
Purpose: News feed content — text, images, videos, articles, polls
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| post_id | BIGINT (PK) | Unique identifier |
| author_id | INT (FK) | User who created the post |
| company_id | INT (FK) | If posted as a company (NULL for personal posts) |
| content | TEXT | Post text (max 3,000 characters) |
| post_type | ENUM | text, image, video, document, article, poll, repost |
| media_urls | TEXT | JSON array of media URLs |
| visibility | ENUM | public, connections_only |
| original_post_id | BIGINT (FK) | If this is a repost, reference to original |
| hashtags | TEXT | Extracted hashtags (comma-separated, for search) |
| impressions_count | INT | Number of feed impressions (denormalized) |
| reactions_count | INT | Total reaction count (denormalized) |
| comments_count | INT | Total comment count (denormalized) |
| reposts_count | INT | Total repost count (denormalized) |
| created_at | TIMESTAMP | Post creation time |
| updated_at | TIMESTAMP | Last edit time |
| is_deleted | BOOLEAN | Soft delete flag |
Business Rules:
- Text posts max 3,000 characters
- Posts can be personal or on behalf of a company (via
company_id) - Reposts reference
original_post_id(can have added commentary) - Visibility controls who sees the post in the feed
🔹 POST_REACTIONS — Reactions on posts
Purpose: Reactions on posts (6 types)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| reaction_id | BIGINT (PK) | Unique identifier |
| post_id | BIGINT (FK) | Post being reacted to |
| user_id | INT (FK) | User who reacted |
| reaction_type | ENUM | like, celebrate, support, love, insightful, funny |
| reacted_at | TIMESTAMP | When the reaction was added |
Business Rules:
- One reaction per user per post
- Changing reaction type updates the existing row
- Unique constraint on
(post_id, user_id)
🔹 COMMENTS — Post comments with threading
Purpose: Post comments with nested threading
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| comment_id | BIGINT (PK) | Unique identifier |
| post_id | BIGINT (FK) | Post being commented on |
| author_id | INT (FK) | Comment author |
| parent_comment_id | BIGINT (FK) | Parent comment for replies (NULL = top-level) |
| content | TEXT | Comment text |
| reactions_count | INT | Reaction count on this comment (denormalized) |
| created_at | TIMESTAMP | Comment time |
| updated_at | TIMESTAMP | Last edit time |
| is_deleted | BOOLEAN | Soft delete flag |
Business Rules:
- Self-referencing for nested replies (2 levels: comment and reply)
- Comments can also receive reactions (like posts)
- Soft delete shows "This comment has been removed"
🔹 JOB_POSTINGS — Job listings
Purpose: Job listings posted by companies
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| job_id | INT (PK) | Unique identifier |
| company_id | INT (FK) | Hiring company |
| posted_by | INT (FK) | Recruiter/admin who posted the job |
| title | VARCHAR(200) | Job title |
| description | TEXT | Full job description |
| location | VARCHAR(100) | Job location |
| is_remote | BOOLEAN | Whether remote work is available |
| employment_type | ENUM | full_time, part_time, contract, internship, temporary, volunteer |
| seniority_level | ENUM | intern, entry, associate, mid_senior, director, executive |
| salary_min | DECIMAL | Salary range lower bound |
| salary_max | DECIMAL | Salary range upper bound |
| salary_currency | VARCHAR(3) | Currency code (USD, INR, EUR, etc.) |
| required_skills | TEXT | JSON array of required skill IDs |
| easy_apply_enabled | BOOLEAN | Whether one-click apply is enabled |
| application_url | VARCHAR(500) | External application link (if not Easy Apply) |
| applicant_count | INT | Number of applicants (denormalized) |
| status | ENUM | active, closed, draft, expired |
| posted_at | TIMESTAMP | Listing date |
| closes_at | TIMESTAMP | Application deadline |
Business Rules:
- Jobs must be associated with a company
- Easy Apply uses the applicant's LinkedIn profile data
- Salary range is optional (many companies don't disclose)
- Jobs can be promoted/sponsored for higher visibility
🔹 JOB_APPLICATIONS — Application tracking
Purpose: Application tracking (bridges applicants and job postings)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| application_id | INT (PK) | Unique identifier |
| job_id | INT (FK) | Applied job posting |
| applicant_id | INT (FK) | Applying user |
| resume_url | VARCHAR(500) | Uploaded resume (optional for Easy Apply) |
| cover_letter | TEXT | Optional cover letter |
| status | ENUM | applied, under_review, interviewing, offered, rejected, withdrawn |
| applied_at | TIMESTAMP | Application timestamp |
| status_updated_at | TIMESTAMP | Last status change |
| recruiter_notes | TEXT | Internal notes (visible only to recruiter) |
Business Rules:
- One application per user per job (UNIQUE on
job_id, applicant_id) - Status workflow: applied → under_review → interviewing → offered/rejected
- Applicants can withdraw at any stage
- Easy Apply auto-populates from profile (no resume upload needed)
🔹 CONVERSATIONS — Messaging threads
Purpose: Messaging threads (1:1 or group)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| conversation_id | BIGINT (PK) | Unique identifier |
| is_group | BOOLEAN | Whether this is a group conversation |
| title | VARCHAR(200) | Group conversation name (NULL for 1:1) |
| created_by | INT (FK) | User who initiated the conversation |
| created_at | TIMESTAMP | Creation timestamp |
| participant_count | INT | Number of participants |
🔹 MESSAGES — Individual messages
Purpose: Individual messages within conversations
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| message_id | BIGINT (PK) | Unique identifier |
| conversation_id | BIGINT (FK) | Parent conversation |
| sender_id | INT (FK) | Message sender |
| content | TEXT | Message text |
| message_type | ENUM | text, image, file, voice, inmail, system |
| attachment_url | VARCHAR(500) | File/image URL (optional) |
| is_inmail | BOOLEAN | Whether this is a paid InMail |
| is_read | BOOLEAN | Read status (for 1:1 chats) |
| sent_at | TIMESTAMP | Send timestamp |
Business Rules:
- InMail messages are sent to non-connections (requires Premium)
- System messages track events like "X added Y to the conversation"
- Group conversations max 50 participants
🔹 NOTIFICATIONS — Activity alerts and triggers
Purpose: Activity alerts — profile views, reactions, connection requests, job updates
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| notification_id | BIGINT (PK) | Unique identifier |
| user_id | INT (FK) | Notification recipient |
| type | ENUM | connection_request, connection_accepted, post_reaction, post_comment, mention, profile_view, job_alert, endorsement, recommendation_request, birthday, work_anniversary |
| actor_id | INT (FK) | Who triggered the notification |
| entity_type | VARCHAR(50) | Type of related entity (post, job, profile, etc.) |
| entity_id | BIGINT | ID of the related entity |
| content | VARCHAR(500) | Pre-rendered notification text |
| is_read | BOOLEAN | Whether the user has seen it |
| created_at | TIMESTAMP | Notification time |
Business Rules:
- Notifications are aggregated for high-volume events ("Alice and 99 others reacted to your post")
- Profile view notifications respect the viewer's privacy settings (anonymous mode)
- Job alert notifications based on saved searches
Entity Summary
| Entity | Type | Purpose |
|---|---|---|
| users | Core | Professional identity |
| work_experience | Core | Employment history |
| education | Core | Academic background |
| companies | Core | Organization profiles |
| company_admins | Junction | Page administration |
| company_followers | Junction | Company follow relationships |
| connections | Core | Bidirectional professional links |
| follows | Supporting | Unidirectional follows |
| skills | Reference | Global skills taxonomy |
| user_skills | Junction | Skills on profiles |
| endorsements | Junction | Skill validations |
| recommendations | Core | Written testimonials |
| certifications | Supporting | Professional credentials |
| projects | Supporting | Portfolio items |
| posts | Core | Feed content |
| post_reactions | Junction | Post engagement |
| comments | Core | Discussion threading |
| job_postings | Core | Job marketplace |
| job_applications | Junction | Application tracking |
| conversations | Core | Messaging threads |
| messages | Core | Individual messages |
| notifications | Supporting | Activity alerts |
arrow_back Previous Topic
Problem Statement Analysis
Next Topic arrow_forward
Relationships Explained